﻿using SqlSugar;
using System.Reflection;
using WebApplication1.Model;

namespace WebApplication1.Data
{
    public class SqlSugarHelper
    {
        public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig() 
        {
            ConnectionString= "Data Source=.;Initial Catalog=BookMS;Persist Security Info=True;User ID=sa;Password=123456;Trust Server Certificate=True",
            DbType=DbType.SqlServer,//数据库类型
            IsAutoCloseConnection=true,//不手动colse 自动关闭连接
            
        },
            db =>
            {
                db.Aop.OnLogExecuting = (sql, pars) =>
                {
                    Console.WriteLine(sql);//输出sql返回的数据，不影响性能开发阶段可以使用
                };
            });

        /// <summary>
        /// 初始化数据库
        /// </summary>
        /// <returns></returns>
        public static string InitDateBase()//自动创建表使用sqlSuger的配置
        {
            try
            {
                //创建数据库
                Db.DbMaintenance.CreateDatabase();
                //创建表
                string nspace = "WebApplication1.Model";
                Type[] ass=Assembly.LoadFrom(AppContext.BaseDirectory+ "WebApplication1.dll").GetTypes().Where(p=>p.Namespace == nspace).ToArray();
                Db.CodeFirst.SetStringDefaultLength(200).InitTables(ass);
                //初始化数据
                Db.Deleteable<User>().ExecuteCommand();//清空数据
                List<User> list = new List<User>();
                for(int i = 1; i <= 5; i++)
                {
                    list.Add(new User()
                    {
                        Id = Guid.NewGuid().ToString(),
                        Name="Tom"+i,
                        Date=DateTime.Now,
                        Address="北京路138号",
                        Order=i,
                    });
                }
                for (int i = 6; i <= 10; i++)
                {
                    list.Add(new User()
                    {
                        Id = Guid.NewGuid().ToString(),
                        Name = "Tom" + i,
                        Date = DateTime.Now,
                        Address = "北京路138号",
                        Order = i,
                    });
                }
                for (int i = 11; i <= 30; i++)
                {
                    list.Add(new User()
                    {
                        Id = Guid.NewGuid().ToString(),
                        Name = "Tom" + i,
                        Date = DateTime.Now,
                        Address = "北京路138号",
                        Order = i,
                    });
                }
                Db.Insertable(list).ExecuteCommand();
                return "ok";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        ///<summary>
        ///1.读取用户列表
        ///</summary>
        public static Result GetUsers(Model req)
        {
            Result result = new Result();
            int total = 0;
            result.Res = Db.Queryable<User>()
                .WhereIF(!string.IsNullOrEmpty(req.KeyWord), s => s.Name.Contains(req.KeyWord) || s.Address.Contains(req.KeyWord))//过滤数据
                .OrderBy(s => s.Order)//排序
                .ToOffsetPage(req.PageIndex, req.PageSize, ref total);//分页
            result.Total = total;
            return result;
        }
        ///<summary>
        ///2.新增
        ///</summary>
        public static bool Add(AddReq req)
        {
            User info =new User()
            {
                Id=Guid.NewGuid().ToString(),
                Name = req.Name,
                Date=req.Date,
                Address=req.Address,
                Order=req.Order
            };
            if (Db.Queryable<User>().Any(p => p.Name == req.Name))
            {
                return false;
            }
            return Db.Insertable(info).ExecuteCommand()>0;
        }
        ///<summary>
        ///3.编辑
        ///</summary>
        public static bool Edit(User req)
        {
            User info =Db.Queryable<User>().First(p=>p.Id==req.Id);
            if (info == null)
            {
                return false;
            }
            info.Name = req.Name;
            info.Date = req.Date;
            info.Address = req.Address;
            info.Order = req.Order;
            return Db.Updateable(info).ExecuteCommand() > 0;//差不多是返回影响行数的意思
            
        }
        ///<summary>
        ///4.删除方法（单个及批量都可以）
        ///</summary>
        public static bool Del(string ids)
        {
            return Db.Ado.ExecuteCommand($"DELETE [User] WHERE Id IN({ids})")>0;
        }
    }

    public class Model//分页
    {
        public string KeyWord { get; set; }
        public int PageIndex { get; set; }
        public int PageSize { get; set; }
        public int Total {  get; set; }
    }
    public class Result//结果
    {
        public int Total { get; set; }
        public object Res { get; set; }
    }
    public class AddReq//添加的请求参数
    {
        //public string Id { get; set; }
        public string Name { get; set; }
        public DateTime Date { get; set; }
        public string Address { get; set; }
        public int Order { get; set; }
    }
}
